use std::borrow::Cow;

use calamine::Data;
use chrono::{DateTime, Datelike, NaiveDate};
use chrono_tz::Tz;
use clickhouse_rs::{
    types::{DateTimeType, Decimal, SqlType},
    Block,
};
use shq_common::prelude::{
    clickhouse::{get_columns, str_to_decmial},
    excel_float_to_datetime, get_ch_pool, get_date_1970, get_date_time_1900, get_date_time_1970,
    parse_date, parse_date_time, parse_iso8601_duration,
};

use crate::definition::{CellValue, DbTable, SaveMode, Table};
pub(super) struct TableDatas<'a> {
    datas: Vec<TableData<'a>>,
}
impl<'a> TableDatas<'a> {
    pub(super) fn new() -> Self {
        TableDatas { datas: Vec::new() }
    }
    pub(super) async fn add_table_data(
        &mut self,
        sheet_name: &'a str,
        table: &'a Table,
        db_table: &'a DbTable,
        col_datas: Vec<ColumnDatas<'a>>,
        start_row: Option<u32>,
    ) -> anyhow::Result<()> {
        let mut td = TableData::new(sheet_name, table, db_table, start_row).await?;
        for col_data in col_datas.into_iter() {
            td.add_cloumn_datas(col_data);
        }
        self.datas.push(td);
        Ok(())
    }
    pub(super) async fn save(&self) -> anyhow::Result<()> {
        for td in self.datas.iter() {
            td.save().await?;
        }
        Ok(())
    }
}
pub(super) struct TableData<'a> {
    sheet_name: &'a str,
    table: &'a Table,
    db_table: &'a DbTable,
    columns: Vec<(String, SqlType)>,
    data: Vec<ColumnDatas<'a>>,
    start_row: Option<u32>,
}
impl<'a> TableData<'a> {
    pub(super) async fn new(
        sheet_name: &'a str,
        table: &'a Table,
        db_table: &'a DbTable,
        start_row: Option<u32>,
    ) -> anyhow::Result<Self> {
        let columns = get_columns(db_table.table_name.as_str()).await?;
        let td = TableData {
            sheet_name,
            table,
            db_table,
            columns,
            data: Vec::new(),
            start_row,
        };
        Ok(td)
    }
    pub(super) fn add_cloumn_datas(&mut self, col_data: ColumnDatas<'a>) {
        if col_data.data.len() == 0 {
            return;
        }
        self.data.push(col_data);
    }
    pub(super) async fn clear(&self) -> anyhow::Result<()> {
        let mut key_values = Vec::new();
        self.data.iter().for_each(|d| {
            if let CellData::Str(v) = d.data[0] {
                key_values.push((d.col_name, v));
            }
        });
        let mut where_cause: String = String::new();
        let klen = key_values.len();
        if klen > 0 {
            where_cause.push_str(" where ");
        }
        key_values.into_iter().enumerate().for_each(|(i, (k, v))| {
            let wc = format!(" {}='{}'", k, v);
            where_cause.push_str(wc.as_str());
            if i < klen - 1 {
                where_cause.push_str(" and ");
            }
        });
        let table_name = self.db_table.table_name.as_str();
        let mut ins_cols: String = String::new();
        let mut sel_cols: String = String::new();
        let mut group_bys: Vec<&str> = Vec::new();
        let mut having_cause: Vec<String> = Vec::new();
        for (i, d) in self.data.iter().enumerate() {
            ins_cols.push_str(d.col_name);
            let (_, st) = self
                .columns
                .iter()
                .find(|(c, _)| c == d.col_name)
                .ok_or_else(|| anyhow::anyhow!("{}列不存在", d.col_name))?;
            let is_num = match st {
                SqlType::UInt32 | SqlType::UInt64 | SqlType::UInt128 => true,
                SqlType::Int32 | SqlType::Int64 | SqlType::Int128 => true,
                SqlType::Float32 | SqlType::Float64 | SqlType::Decimal(_, _) => true,
                _ => false,
            };
            let is_unique_key = self.db_table.unique_keys.iter().any(|k| k == d.col_name);
            if is_unique_key {
                sel_cols.push_str(d.col_name);
                group_bys.push(d.col_name);
            } else if is_num {
                sel_cols.push_str(format!("-sum({}) as {}", d.col_name, d.col_name).as_str());
                having_cause.push(format!("{}!=0", d.col_name));
            } else {
                sel_cols.push_str(format!("anyLast({}) as {}", d.col_name, d.col_name).as_str());
            }
            if i < self.data.len() - 1 {
                ins_cols.push(',');
                sel_cols.push(',');
            }
        }
        let having_cause = having_cause.join(" or ");
        let group_by = group_bys.join(",");
        let sql_str = format!("insert into {table_name}({ins_cols}) select {sel_cols} from {table_name} {where_cause} group by {group_by} having {having_cause}");
        let mut client = get_ch_pool().get_handle().await?;
        client.execute(sql_str.as_str()).await?;
        Ok(())
    }
    pub(super) async fn save(&self) -> anyhow::Result<()> {
        if self.db_table.save_mode == SaveMode::Overwrite {
            self.clear().await?;
        }
        let block = self
            .data
            .iter()
            .try_fold(Block::new(), |block, d| {
                let (_, st) = self
                    .columns
                    .iter()
                    .find(|c| c.0 == d.col_name)
                    .ok_or_else(|| anyhow::anyhow!("{}列不存在", d.col_name))?;
                add_block_column(self.table, block, st, d, self.start_row)
            })
            .or_else(|err| Err(anyhow::anyhow!("sheet:{},{}", self.sheet_name, err)))?;
        let mut client = get_ch_pool().get_handle().await?;
        client
            .insert(self.db_table.table_name.as_str(), block)
            .await
            .or_else(|err| Err(anyhow::anyhow!("sheet:{},{}", self.sheet_name, err)))?;
        Ok(())
    }
}
#[derive(Debug)]
pub(super) struct ColumnDatas<'a> {
    col_name: &'a str,
    data: Vec<CellData<'a>>,
}
impl<'a> ColumnDatas<'a> {
    pub(super) fn new(col_name: &'a str, data: Vec<CellData<'a>>) -> Self {
        ColumnDatas { col_name, data }
    }
    pub(super) fn to_vec_str(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<String>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_str()
                    .map(|v| v.into_owned())
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_bool(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<bool>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_bool()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_u8(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<u8>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_u8()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_u16(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<u16>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_u16()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_u32(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<u32>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_u32()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_u64(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<u64>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_u64()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_i8(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<i8>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_i8()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_i16(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<i16>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_i16()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_i32(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<i32>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_i32()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_i64(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<i64>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_i64()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_f32(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<f32>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_f32()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_f64(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<f64>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_f64()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_decimal(
        &self,
        p: u8,
        s: u8,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<Decimal>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_decimal(p, s)
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_date(
        &self,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<NaiveDate>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_date()
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
    pub(super) fn to_vec_date_time(
        &self,
        dtt: &DateTimeType,
        cell_val: &CellValue,
        start_row: Option<u32>,
    ) -> anyhow::Result<Vec<DateTime<Tz>>> {
        self.data
            .iter()
            .enumerate()
            .map(|(i, d)| {
                d.to_date_time(dtt)
                    .map_err(|err| parse_err(cell_val, start_row, i as u32, err))
            })
            .collect()
    }
}

#[derive(Debug, Clone)]
pub(super) enum CellData<'a> {
    Int(i64),
    Str(&'a str),
    Data(&'a Data),
    None,
}
impl<'a> CellData<'a> {
    // pub(super) fn from_str(s: Option<&'a str>) -> Self {
    //     if let Some(s) = s {
    //         CellData::Str(s)
    //     } else {
    //         CellData::None
    //     }
    // }
    pub(super) fn from_data(d: Option<&'a Data>) -> Self {
        if let Some(d) = d {
            CellData::Data(d)
        } else {
            CellData::None
        }
    }
}
impl<'a> CellData<'a> {
    pub(super) fn to_u8(&self) -> anyhow::Result<u8> {
        match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}转换为u8失败:负数", i));
                }
                if *i > u8::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为u8失败:超过u8最大值", i));
                }
                Ok(*i as u8)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为u8失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < 0 {
                        return Err(anyhow::anyhow!("{}转换为u8失败:负数", i));
                    }
                    if *i > u8::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为u8失败:超过u8最大值", i));
                    }
                    Ok(*i as u8)
                }
                Data::Float(f) => {
                    if *f < 0.0 {
                        return Err(anyhow::anyhow!("{}转换为u8失败:负数", f));
                    }
                    if *f > u8::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为u8失败:超过u8最大值", f));
                    }
                    Ok(*f as u8)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为u8失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的u8"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_u16(&self) -> anyhow::Result<u16> {
        match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}转换为u8失败:负数", i));
                }
                if *i > u16::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为u8失败:超过u8最大值", i));
                }
                Ok(*i as u16)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为u16失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < 0 {
                        return Err(anyhow::anyhow!("{}转换为u16失败:负数", i));
                    }
                    if *i > u16::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为u16失败:超过u16最大值", i));
                    }
                    Ok(*i as u16)
                }
                Data::Float(f) => {
                    if *f < 0.0 {
                        return Err(anyhow::anyhow!("{}转换为u16失败:负数", f));
                    }
                    if *f > u16::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为u16失败:超过u16最大值", f));
                    }
                    Ok(*f as u16)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为u16失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的u16"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_u32(&self) -> anyhow::Result<u32> {
        match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}转换为u32失败:负数", i));
                }
                if *i > u32::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为u32失败:超过u32最大值", i));
                }
                Ok(*i as u32)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为u32失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < 0 {
                        return Err(anyhow::anyhow!("{}转换为u32失败:负数", i));
                    }
                    if *i > u32::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为u32失败:超过u32最大值", i));
                    }
                    Ok(*i as u32)
                }
                Data::Float(f) => {
                    if *f < 0.0 {
                        return Err(anyhow::anyhow!("{}转换为u32失败:负数", f));
                    }
                    if *f > u32::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为u32失败:超过u32最大值", f));
                    }
                    Ok(*f as u32)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为u32失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的u32"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_u64(&self) -> anyhow::Result<u64> {
        match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}转换为u64失败:负数", i));
                }
                if *i > u64::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为u64失败:超过u64最大值", i));
                }
                Ok(*i as u64)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为u64失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < 0 {
                        return Err(anyhow::anyhow!("{}转换为u64失败:负数", i));
                    }
                    if *i > u64::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为u64失败:超过u64最大值", i));
                    }
                    Ok(*i as u64)
                }
                Data::Float(f) => {
                    if *f < 0.0 {
                        return Err(anyhow::anyhow!("{}转换为u64失败:负数", f));
                    }
                    if *f > u64::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为u64失败:超过u64最大值", f));
                    }
                    Ok(*f as u64)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为u64失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的u64"),
            },
            CellData::None => Ok(0),
        }
    }
}

impl<'a> CellData<'a> {
    pub(super) fn to_i8(&self) -> anyhow::Result<i8> {
        match self {
            CellData::Int(i) => {
                if *i < i8::MIN as i64 {
                    return Err(anyhow::anyhow!("{}转换为i8失败:小于i8最小值", i));
                }
                if *i > i8::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为i8失败:大于i8最大值", i));
                }
                Ok(*i as i8)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为i8失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < i8::MIN as i64 {
                        return Err(anyhow::anyhow!("{}转换为i8失败:小于i8最小值", i));
                    }
                    if *i > i8::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为i8失败:大于i8最大值", i));
                    }
                    Ok(*i as i8)
                }
                Data::Float(f) => {
                    if *f < i8::MIN as f64 {
                        return Err(anyhow::anyhow!("{}转换为i8失败:小于i8最小值", f));
                    }
                    if *f > i8::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为i8失败:大于i8最大值", f));
                    }
                    Ok(*f as i8)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为i8失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的i8"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_i16(&self) -> anyhow::Result<i16> {
        match self {
            CellData::Int(i) => {
                if *i < i16::MIN as i64 {
                    return Err(anyhow::anyhow!("{}转换为i16失败:小于i16最小值", i));
                }
                if *i > i16::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为i16失败:大于i16最大值", i));
                }
                Ok(*i as i16)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为i16失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < i16::MIN as i64 {
                        return Err(anyhow::anyhow!("{}转换为i16失败:小于i16最小值", i));
                    }
                    if *i > i16::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为i16失败:大于i16最大值", i));
                    }
                    Ok(*i as i16)
                }
                Data::Float(f) => {
                    if *f < i16::MIN as f64 {
                        return Err(anyhow::anyhow!("{}转换为i16失败:小于i16最小值", f));
                    }
                    if *f > i16::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为i16失败:大于i16最大值", f));
                    }
                    Ok(*f as i16)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为i16失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的i16"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_i32(&self) -> anyhow::Result<i32> {
        match self {
            CellData::Int(i) => {
                if *i < i32::MIN as i64 {
                    return Err(anyhow::anyhow!("{}转换为i32失败:小于i32最小值", i));
                }
                if *i > i32::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为i32失败:大于i32最大值", i));
                }
                Ok(*i as i32)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为i32失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => {
                    if *i < i32::MIN as i64 {
                        return Err(anyhow::anyhow!("{}转换为i32失败:小于i32最小值", i));
                    }
                    if *i > i32::MAX as i64 {
                        return Err(anyhow::anyhow!("{}转换为i32失败:大于i32最大值", i));
                    }
                    Ok(*i as i32)
                }
                Data::Float(f) => {
                    if *f < i32::MIN as f64 {
                        return Err(anyhow::anyhow!("{}转换为i32失败:小于i32最小值", f));
                    }
                    if *f > i32::MAX as f64 {
                        return Err(anyhow::anyhow!("{}转换为i32失败:大于i32最大值", f));
                    }
                    Ok(*f as i32)
                }
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为i32失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的i32"),
            },
            CellData::None => Ok(0),
        }
    }
    pub(super) fn to_i64(&self) -> anyhow::Result<i64> {
        match self {
            CellData::Int(i) => {
                if *i < i64::MIN {
                    return Err(anyhow::anyhow!("{}转换为i64失败:小于i64最小值", i));
                }
                if *i > i64::MAX {
                    return Err(anyhow::anyhow!("{}转换为i64失败:大于i64最大值", i));
                }
                Ok(*i)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为i64失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => Ok(*i),
                Data::Float(f) => Ok(*f as i64),
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为i64失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::DurationIso(dura_iso) => {
                    match parse_iso8601_duration(dura_iso) {
                        Ok(seconds) => {
                            // 返回秒数
                            Ok(seconds)
                        }
                        Err(_) => {
                            // 如果解析失败，保留原始字符串
                            anyhow::bail!("{dura_iso}不是有效的ISO 8601 duration");
                        }
                    }
                }
                Data::Empty | Data::Error(_) => Ok(0),
                _ => anyhow::bail!("Cell:{d}不是有效的i64"),
            },
            CellData::None => Ok(0),
        }
    }
}

impl<'a> CellData<'a> {
    pub(super) fn to_f32(&self) -> anyhow::Result<f32> {
        match self {
            CellData::Int(i) => {
                if *i < f32::MIN as i64 {
                    return Err(anyhow::anyhow!("{}转换为f32失败:小于f32最小值", i));
                }
                if *i > f32::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为f32失败:大于f32最大值", i));
                }
                Ok(*i as f32)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0.0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为f32失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => Ok(*i as f32),
                Data::Float(f) => Ok(*f as f32),
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0.0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为f32失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0.0),
                _ => anyhow::bail!("Cell:{d}不是有效的f32"),
            },
            CellData::None => Ok(0.0),
        }
    }
    pub(super) fn to_f64(&self) -> anyhow::Result<f64> {
        match self {
            CellData::Int(i) => {
                if *i < f64::MIN as i64 {
                    return Err(anyhow::anyhow!("{}转换为f64失败:小于f64最小值", i));
                }
                if *i > f64::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为f64失败:大于f64最大值", i));
                }
                Ok(*i as f64)
            }
            CellData::Str(s) => {
                if s.is_empty() || *s == "-" {
                    return Ok(0.0);
                }
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为f64失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => Ok(*i as f64),
                Data::Float(f) => Ok(*f),
                Data::String(s) => {
                    if s.is_empty() || *s == "-" {
                        return Ok(0.0);
                    }
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为f64失败:{}", s, err)))?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(0.0),
                _ => anyhow::bail!("{d}不是有效的f64"),
            },
            CellData::None => Ok(0.0),
        }
    }
    pub(super) fn to_decimal(&self, p: u8, s: u8) -> anyhow::Result<Decimal> {
        match self {
            CellData::Int(i) => {
                if *i < i64::MIN as i64 || *i > i64::MAX as i64 {
                    return Err(anyhow::anyhow!("{}转换为Decimal失败:超出范围", i));
                }
                Ok(Decimal::of(*i, 0))
            }
            CellData::Str(sv) => {
                if sv.is_empty() || *sv == "-" {
                    return Ok(Decimal::default());
                }
                let v = str_to_decmial(sv, p, s)?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Int(i) => Ok(Decimal::of(*i, 0)),
                Data::Float(f) => Ok(Decimal::of(*f, s)),
                Data::String(sv) => {
                    if sv.is_empty() || *sv == "-" {
                        return Ok(Decimal::default());
                    }
                    let v = str_to_decmial(sv, p, s)?;
                    Ok(v)
                }
                Data::Empty | Data::Error(_) => Ok(Decimal::default()),
                _ => anyhow::bail!("{d}不是有效的Decimal"),
            },
            CellData::None => Ok(Decimal::default()),
        }
    }
}

impl<'a> CellData<'a> {
    pub(super) fn to_str(&self) -> anyhow::Result<Cow<'_, str>> {
        match self {
            CellData::Int(i) => Ok(Cow::Owned(i.to_string())),
            CellData::Str(s) => Ok(Cow::Borrowed(s)),
            CellData::Data(d) => match d {
                Data::Bool(b) => Ok(Cow::Borrowed(if *b { "是" } else { "否" })),
                Data::Int(i) => Ok(Cow::Owned(i.to_string())),
                Data::Float(f) => Ok(Cow::Owned(f.to_string())),
                Data::String(s) => Ok(Cow::Borrowed(s)),
                Data::DateTime(dt) => {
                    let fv = dt.as_f64();
                    if let Some(ndt) = excel_float_to_datetime(fv, false) {
                        Ok(Cow::Owned(ndt.to_string()))
                    } else {
                        anyhow::bail!("{dt}不是有效的日期")
                    }
                }
                Data::DateTimeIso(dt) => Ok(Cow::Borrowed(dt)),
                Data::DurationIso(dura_iso) => Ok(Cow::Borrowed(dura_iso)),
                Data::Empty => Ok(Cow::Borrowed("")),
                _ => anyhow::bail!("{d}不是有效的字符串"),
            },
            CellData::None => Ok(Cow::Borrowed("")),
        }
    }
    pub(super) fn to_bool(&self) -> anyhow::Result<bool> {
        match self {
            CellData::Int(i) => {
                if *i == 0 {
                    return Ok(false);
                }
                if *i == 1 {
                    return Ok(true);
                }
                Err(anyhow::anyhow!("{}转换为bool失败:不是0或1", i))
            }
            CellData::Str(s) => {
                let v = s
                    .parse()
                    .or_else(|err| Err(anyhow::anyhow!("{}转换为bool失败:{}", s, err)))?;
                Ok(v)
            }
            CellData::Data(d) => match d {
                Data::Bool(b) => Ok(*b),
                Data::Int(i) => Ok(*i != 0),
                Data::Float(f) => Ok(*f != 0.0),
                Data::String(s) => {
                    let v = s
                        .parse()
                        .or_else(|err| Err(anyhow::anyhow!("{}转换为bool失败:{}", s, err)))?;
                    Ok(v)
                }
                _ => anyhow::bail!("Cell:{d}不是有效的bool"),
            },
            CellData::None => Ok(false),
        }
    }
    pub(super) fn to_date(&self) -> anyhow::Result<NaiveDate> {
        let val = match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}不是有效的日期", i));
                }
                let fv = *i as f64;
                if let Some(ndt) = excel_float_to_datetime(fv, false) {
                    ndt.date()
                } else {
                    anyhow::bail!("{}不是有效的日期", i);
                }
            }
            CellData::Str(s) => {
                let v = parse_date(s)?;
                v
            }
            CellData::Data(d) => match d {
                Data::DateTime(dt) => {
                    let fv = dt.as_f64();
                    if let Some(ndt) = excel_float_to_datetime(fv, false) {
                        ndt.date()
                    } else {
                        anyhow::bail!("Cell:{dt}不是有效的日期")
                    }
                }
                Data::DateTimeIso(dt) => {
                    let v = dt.to_string();
                    let nv = parse_date(&v)?;
                    nv
                }
                Data::String(s) => {
                    let v = parse_date(s)?;
                    v
                }
                _ => anyhow::bail!("Cell:{d}不是有效的日期"),
            },
            CellData::None => get_date_1970(),
        };
        if val.year() < 1970 {
            return Ok(get_date_1970());
        }
        Ok(val)
    }
    pub(super) fn to_date_time(&self, dtt: &DateTimeType) -> anyhow::Result<DateTime<Tz>> {
        let val = match self {
            CellData::Int(i) => {
                if *i < 0 {
                    return Err(anyhow::anyhow!("{}不是有效的日期", i));
                }
                let fv = *i as f64;
                if let Some(ndt) = excel_float_to_datetime(fv, false) {
                    ndt.and_utc()
                        .naive_utc()
                        .and_local_timezone(chrono_tz::UTC)
                        .single()
                        .ok_or_else(|| {
                            anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                        })?
                } else {
                    anyhow::bail!("{}不是有效的日期", i);
                }
            }
            CellData::Str(s) => {
                let v = parse_date_time(s)?;
                v.naive_utc()
                    .and_local_timezone(chrono_tz::UTC)
                    .single()
                    .ok_or_else(|| anyhow::anyhow!("Ambiguous or invalid timezone conversion"))?
            }
            CellData::Data(d) => match d {
                Data::DateTime(dt) => {
                    let fv = dt.as_f64();
                    if let Some(ndt) = excel_float_to_datetime(fv, false) {
                        ndt.and_local_timezone(chrono_tz::UTC)
                            .single()
                            .ok_or_else(|| {
                                anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                            })?
                    } else {
                        anyhow::bail!("Cell:{dt}不是有效的日期")
                    }
                }
                Data::DateTimeIso(dt) => {
                    let v = dt.to_string();
                    let nv = parse_date_time(&v)?;
                    nv.naive_utc()
                        .and_local_timezone(chrono_tz::UTC)
                        .single()
                        .ok_or_else(|| {
                            anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                        })?
                }
                Data::String(s) => {
                    let v = parse_date_time(s)?;
                    v.naive_utc()
                        .and_local_timezone(chrono_tz::UTC)
                        .single()
                        .ok_or_else(|| {
                            anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                        })?
                }
                _ => {
                    anyhow::bail!("Cell:{d}不是有效的日期")
                }
            },
            CellData::None => {
                let ndt = get_date_time_1900();
                ndt.and_local_timezone(chrono_tz::UTC)
                    .single()
                    .ok_or_else(|| anyhow::anyhow!("Ambiguous or invalid timezone conversion"))?
            }
        };
        match dtt {
            DateTimeType::DateTime32 | DateTimeType::Chrono => {
                if val.year() < 1970 {
                    let ndt = get_date_time_1970();
                    let nv = ndt
                        .and_local_timezone(chrono_tz::UTC)
                        .single()
                        .ok_or_else(|| {
                            anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                        })?;
                    return Ok(nv);
                }
            }
            DateTimeType::DateTime64(_, _) => {
                if val.year() < 1900 {
                    let ndt = get_date_time_1900();
                    let nv = ndt
                        .and_local_timezone(chrono_tz::UTC)
                        .single()
                        .ok_or_else(|| {
                            anyhow::anyhow!("Ambiguous or invalid timezone conversion")
                        })?;
                    return Ok(nv);
                }
            }
        }
        Ok(val)
    }
}
fn add_block_column<'a>(
    table: &Table,
    block: Block,
    st: &'a SqlType,
    col_data: &'a ColumnDatas<'a>,
    start: Option<u32>,
) -> anyhow::Result<Block> {
    let col = table
        .columns
        .iter()
        .find(|c| c.col_name.as_str() == col_data.col_name)
        .ok_or_else(|| anyhow::anyhow!("{}列不存在", col_data.col_name))?;
    let cell_val = &col.cell_value;
    let b = match st {
        SqlType::Bool => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_bool(cell_val, start)?))
        }
        SqlType::UInt8 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_u8(cell_val, start)?))
        }
        SqlType::UInt16 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_u16(cell_val, start)?))
        }
        SqlType::UInt32 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_u32(cell_val, start)?))
        }
        SqlType::UInt64 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_u64(cell_val, start)?))
        }
        SqlType::Int8 => Ok(block.column(&col_data.col_name, col_data.to_vec_i8(cell_val, start)?)),
        SqlType::Int16 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_i16(cell_val, start)?))
        }
        SqlType::Int32 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_i32(cell_val, start)?))
        }
        SqlType::Int64 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_i64(cell_val, start)?))
        }
        SqlType::String => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_str(cell_val, start)?))
        }
        SqlType::FixedString(_) => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_str(cell_val, start)?))
        }
        SqlType::Float32 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_f32(cell_val, start)?))
        }
        SqlType::Float64 => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_f64(cell_val, start)?))
        }
        SqlType::Date => {
            Ok(block.column(&col_data.col_name, col_data.to_vec_date(cell_val, start)?))
        }
        SqlType::DateTime(v) => Ok(block.column(
            &col_data.col_name,
            col_data.to_vec_date_time(v, cell_val, start)?,
        )),
        SqlType::LowCardinality(t) => add_block_column(table, block, t, col_data, start),
        SqlType::Decimal(p, s) => Ok(block.column(
            &col_data.col_name,
            col_data.to_vec_decimal(*p, *s, cell_val, start)?,
        )),
        _ => Err(anyhow::anyhow!("{}不支持的类型", st)),
    };
    b
}

pub(super) async fn has_in_db(
    table_name: &str,
    key_values: &[(&str, &str)],
) -> anyhow::Result<bool> {
    let mut sql_str = format!("select count(*) from {}", table_name);
    let klen = key_values.len();
    if klen > 0 {
        sql_str.push_str(" where ");
    }
    key_values.into_iter().enumerate().for_each(|(i, (k, v))| {
        let wc = format!(" {}='{}'", k, v);
        sql_str.push_str(wc.as_str());
        if i < klen - 1 {
            sql_str.push_str(" and ");
        }
    });
    let mut client = get_ch_pool().get_handle().await?;
    let block = client.query(sql_str.as_str()).fetch_all().await?;
    let mut count = 0u64;
    if let Some(row) = block.rows().next() {
        count = row.get(0)?
    }
    Ok(count > 0)
}
fn parse_err(
    cell_val: &CellValue,
    start_row: Option<u32>,
    offset: u32,
    err: anyhow::Error,
) -> anyhow::Error {
    match cell_val {
        CellValue::Col(c) => anyhow::anyhow!("{c}{}:{}", start_row.unwrap_or(0) + offset, err),
        CellValue::Cell(c) => anyhow::anyhow!("{c}:{}", err),
        CellValue::Param(p) => anyhow::anyhow!("参数{p}:{}", err),
        CellValue::SheetName => anyhow::anyhow!("SheetName:{}", err),
        CellValue::Seek(i) => anyhow::anyhow!("Seek{i}:{}", err),
        CellValue::CrossID => anyhow::anyhow!("CrossID:{}", err),
        CellValue::CrossValue => anyhow::anyhow!("CrossValue:{}", err),
        CellValue::RowNo => anyhow::anyhow!("RowNo:{}", err),
    }
}
